﻿using System;
using System.Collections.Generic;
using System.Text;
using RFIDStockManagement.IDAL;
using System.Data;
using RFIDStockManagement.Model.Public;
using RFIDStockManagement.Common.Public;
using System.Data.Common;

namespace RFIDStockManagement.DAL
{
    public class UserAccess : IUserAccess
    {
        private DbHelper _db;
        private const string SELECT_USERINFO = "Select FID,FLogName,FName,FTelNumber,FSex,FPwd,FLastLoginTime,FLastLogoutTime,FIsAdmin From rfid_User ";
        private const string UPDATE_USERINFO =@"Update rfid_User set FLogName = '{0}',FName= '{1}',FTelNumber= '{2}',
FSex ={3},FIsAdmin = {4}  Where Fid= {5} ";
        private const string INSERT_USERINFO = "Insert Into rfid_User(FLogName,FName,FTelNumber,FSex,FPwd,FIsAdmin) Values(";
        

        public UserAccess()
        {
            _db = new DbHelper();
        }

        public UserAccess(string constr)
        {
            _db = new DbHelper(constr);
        }

        #region IUserAccess 成员

        /// <summary>
        /// 根据ID获取用户信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public UserInfo GetUser(int id)
        {
            string sql = SELECT_USERINFO + " Where FIsDeleted != 1 And Fid = " + id.ToString();
            return GetUserInfoBySql(sql); 
        }

        private UserInfo GetUserInfoFromDataRow(DataRow value)
        {
            UserInfo newUser = new UserInfo();
            newUser.Id = PublicFunction.NotNullInt(value["Fid"]);
            newUser.IsAdmin = PublicFunction.NotNullInt(value["FIsAdmin"]) == 1;
            newUser.Name = PublicFunction.NotNull(value["Fname"]);
            newUser.LogName = PublicFunction.NotNull(value["FLogName"]);
            newUser.Passwords = PublicFunction.NotNull(value["FPwd"]);
            newUser.TelNumber = PublicFunction.NotNull(value["FTelNumber"]);
            newUser.IsMale = PublicFunction.NotNullInt(value["FSex"]) == 1;
            newUser.LastLoginTime = PublicFunction.NotNullDateTime(value["FLastLoginTime"]);
            newUser.LastLogoutTime = PublicFunction.NotNullDateTime(value["FLastLogoutTime"]);

            return newUser;

        }

        /// <summary>
        /// 获取用户列表
        /// </summary>
        /// <returns></returns>
        public List<UserInfo> GetAllUsers()
        {
            return GetUsers(string.Empty);
        }

        /// <summary>
        /// 更新用户信息
        /// </summary>
        /// <param name="curInfo"></param>
        /// <param name="errString"></param>
        /// <returns></returns>
        public bool Update(UserInfo curInfo, out string errString)
        {
            errString = string.Empty;
            string sql = string.Format(UPDATE_USERINFO,curInfo.LogName,curInfo.Name,curInfo.TelNumber,
                curInfo.IsMale?1:0,curInfo.IsAdmin?1:0,curInfo.Id.ToString());
            DbCommand cmd = _db.GetSqlStringCommand(sql);
            _db.ExecuteNonQuery(cmd);
            return true;
        }

        public bool ChangePwd(int id, string pwd)
        {
            string sql = "Update rfid_User Set FPwd ='{0}' Where fid ={1}";
            try
            {
                DbCommand cmd = _db.GetSqlStringCommand(string.Format(sql, pwd, id));
                _db.ExecuteNonQuery(cmd);
                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool AddUser(UserInfo info)
        {
           
            string sql = INSERT_USERINFO;
            sql+= PublicFunction.AddToStringWithCom(info.LogName);
            sql+= PublicFunction.AddToStringWithCom(info.Name);
            sql+= PublicFunction.AddToStringWithCom(info.TelNumber);
            sql+= PublicFunction.AddToValueWithCom(info.IsMale?1:0);
            sql+= PublicFunction.AddToStringWithCom(info.Passwords);
            sql+= info.IsAdmin? "1)":"0)";

            try
            {
                DbCommand cmd = _db.GetSqlStringCommand(sql);
                _db.ExecuteNonQuery(cmd);
                return true;
            }
            catch
            {
                return false;
                throw;
            }

        }

        private UserInfo GetUserInfoBySql(string sql)
        {
            DbCommand cmd = _db.GetSqlStringCommand(sql);
            DataTable dt = _db.ExecuteDataTable(cmd);

            if (dt == null) return null;
            if (dt.Rows.Count <= 0) return null;

            return GetUserInfoFromDataRow(dt.Rows[0]);
        }

        /// <summary>
        /// 获取用户信息
        /// </summary>
        /// <param name="logName">登录名</param>
        /// <param name="pwd">密码</param>
        /// <param name="isAdmin">是否管理员登录</param>
        /// <returns></returns>
        public UserInfo GetUser(string logName, string pwd, bool isAdmin)
        {
            string sql = SELECT_USERINFO + "Where  FIsDeleted != 1 And FlogName='{0}' And FPwd='{1}'";
            sql = string.Format(sql, logName, pwd);
            if (isAdmin)
            {
                sql += " And FIsAdmin = 1";
            }


            return GetUserInfoBySql(sql);

        }

        /// <summary>
        /// 根据过滤条件获取用户信息
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public List<UserInfo> GetUsers(string filter)
        {

            UserInfo newUser;
            List<UserInfo> retVal = new List<UserInfo>();
            
            string sql = SELECT_USERINFO;
            sql += " Where  FIsDeleted != 1 ";
            if (!string.IsNullOrEmpty(filter))
            {
                sql += "  And ";
                sql +=  filter;
            }

            DbCommand cmd = _db.GetSqlStringCommand(sql + "order by FName");
            DataTable dt = _db.ExecuteDataTable(cmd);


            if (dt == null) return retVal;
            if (dt.Rows.Count <= 0) return retVal;

            foreach (DataRow x in dt.Rows)
            {
                newUser = GetUserInfoFromDataRow(x);
                retVal.Add(newUser);
            }

            return retVal;
        }


        public void UpdateUserLoginTime(int id)
        {
            string sql = "Update rfid_User Set FLastLoginTime = GetDate() Where Fid =" + id.ToString();
            DbCommand cmd = _db.GetSqlStringCommand(sql);
            _db.ExecuteNonQuery(cmd);
        }

        public void UpdateUserLongoutTime(int id)
        {
            string sql = "Update rfid_User Set FLastLogoutTime = GetDate() Where Fid =" + id.ToString();
            DbCommand cmd = _db.GetSqlStringCommand(sql);
            _db.ExecuteNonQuery(cmd);
        }


        public string GetUserPwd(int id)
        {
            UserInfo curUser = GetUser(id);
            
            if (curUser != null)
            {
                return curUser.Passwords;
            }
            else
            {
                return string.Empty;
            }
        }


        public bool DeleteUser(int id)
        {
            string sql = "Update rfid_User set FLogName = FLogName +'_Deleted',FIsDeleted = 1 Where fid={0}";
            try
            {
                DbCommand cmd = _db.GetSqlStringCommand(string.Format(sql, id.ToString()));
                _db.ExecuteNonQuery(cmd);
                return true;    
            }
            catch
            {
                return false;
            }
            
        }


        #endregion


    }
}
